package com.augurit.gzcgwsp.excelimportexport.service.impl;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.util.Region;
import org.dozer.DozerBeanMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.augurit.gzcgwsp.excelimportexport.dao.YwRqQyjyxkRqjszrypbqkDao;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkRqjszdjb;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkRqjszrypbqk;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkRqjszrypbqkService;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalRqjszdjb;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalRqjszrypbqk;
import com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkFinalRqjszrypbqkService;

/**
 * 广州市燃气经营许可证许可审批------六、1 人员配备情况
 * @author xgw
 *
 */
@Service
@Transactional
public class YwRqQyjyxkRqjszrypbqkServiceImpl implements IYwRqQyjyxkRqjszrypbqkService{
	
	@Autowired
	private YwRqQyjyxkRqjszrypbqkDao ywRqQyjyxkRqjszrypbqkDao;
	@Autowired
	private IYwRqQyjyxkFinalRqjszrypbqkService rqjszrypbqkService;
	/**
	 * 保存 六、1 人员配备情况
	 * @param ywRqQyjyxkRqjszrypbqk
	 */
	public void save(YwRqQyjyxkRqjszrypbqk ywRqQyjyxkRqjszrypbqk){
		ywRqQyjyxkRqjszrypbqkDao.save(ywRqQyjyxkRqjszrypbqk);
		
	}
	
	/**
	 * 解析 六、1 人员配备情况
	 * @param hssfsheet
	 * @param acceptNo
	 * @param qyid
	 * @return
	 */
	public YwRqQyjyxkRqjszrypbqk parseRqjszrypbqk(HSSFSheet hssfsheet, String acceptNo,Long qyid, String username,Date dateFlag,String flag) {
		String xm = null;//姓名
		String nl = null;//年龄
		String gw = null;//岗位
		String gwjnzsjbh = null;//岗位技能证书及编号
		String fzjg = null;//发证机关
		
		String hql = "from YwRqQyjyxkRqjszrypbqk t where t.qyid = "+qyid+" and t.flag="+flag+"";
			List<YwRqQyjyxkRqjszrypbqk> list=ywRqQyjyxkRqjszrypbqkDao.find(hql);
			for(int i=0;i<list.size();i++)
			{
				ywRqQyjyxkRqjszrypbqkDao.delete(list.get(i));
			}
		
			try {
			
			HSSFRow hssfrow = hssfsheet.getRow(0);// 第一行
			// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
			for (int i = 21; i < hssfsheet.getPhysicalNumberOfRows(); i++) {
				hssfrow = hssfsheet.getRow(i);
				// 判断是否还存在需要导入的数据
				if (hssfrow == null) {
					// System.out.println("这里已没有数据，在第" + i + "列,第" + j + "行");
					break;
				}			
				
				if (hssfrow.getCell((short) 1) == null) {//姓名
					xm = "";
				} else if (hssfrow.getCell((short) 1).getCellType() == 0) {
					xm = new Double(hssfrow.getCell((short) 1).getNumericCellValue()).toString();
				} else {// 如果EXCEL表格中的数据类型为字符串型
					xm = hssfrow.getCell((short) 1).getStringCellValue().trim();
				}
				if (hssfrow.getCell((short) 2) == null) {//年龄
					nl = "";
				} else if (hssfrow.getCell((short) 2).getCellType() == 0) {
					nl = new Double(hssfrow.getCell((short) 2).getNumericCellValue()).toString();
				} else {// 如果EXCEL表格中的数据类型为字符串型
					nl = hssfrow.getCell((short) 2).getStringCellValue().trim();
				}
				if (hssfrow.getCell((short) 3) == null) {//年龄
					gw = "";
				} else if (hssfrow.getCell((short) 3).getCellType() == 0) {
					gw = new Double(hssfrow.getCell((short) 3).getNumericCellValue()).toString();
				} else {// 如果EXCEL表格中的数据类型为字符串型
					gw = hssfrow.getCell((short) 3).getStringCellValue().trim();
				}
				if (hssfrow.getCell((short) 4) == null) {//年龄
					gwjnzsjbh = "";
				} else if (hssfrow.getCell((short) 4).getCellType() == 0) {
					gwjnzsjbh = new Double(hssfrow.getCell((short) 4).getNumericCellValue()).toString();
				} else {// 如果EXCEL表格中的数据类型为字符串型
					gwjnzsjbh = hssfrow.getCell((short) 4).getStringCellValue().trim();
				}
				if (hssfrow.getCell((short) 6) == null) {//年龄
					fzjg = "";
				} else if (hssfrow.getCell((short) 6).getCellType() == 0) {
					fzjg = new Double(hssfrow.getCell((short) 6).getNumericCellValue()).toString();
				} else {// 如果EXCEL表格中的数据类型为字符串型
					fzjg = hssfrow.getCell((short) 6).getStringCellValue().trim();
				}				
					
				if(StringUtils.isEmpty(xm)||"待续".equals(xm)){
					break;
				}else{
					YwRqQyjyxkRqjszrypbqk ywRqQyjyxkRqjszrypbqk = new YwRqQyjyxkRqjszrypbqk();
					ywRqQyjyxkRqjszrypbqk.setQyid(qyid);
					ywRqQyjyxkRqjszrypbqk.setAcceptNo(acceptNo);
					ywRqQyjyxkRqjszrypbqk.setUserName(username);
					ywRqQyjyxkRqjszrypbqk.setUploadDate(new Date());
					ywRqQyjyxkRqjszrypbqk.setFlag(flag);
					ywRqQyjyxkRqjszrypbqk.setXm(xm);
					ywRqQyjyxkRqjszrypbqk.setNl(nl);
					ywRqQyjyxkRqjszrypbqk.setGw(gw);
					ywRqQyjyxkRqjszrypbqk.setGwjnzsjbh(gwjnzsjbh);
					ywRqQyjyxkRqjszrypbqk.setFzjg(fzjg);
					ywRqQyjyxkRqjszrypbqk.setDateFlag(dateFlag);	
					this.save(ywRqQyjyxkRqjszrypbqk);
				}					
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
     * 导出 燃气接收站、储配站、汽车加气站登记表
     * 
     * @param hssfsheet
     * @param acceptNo
     * @param qyid
     * @param username
     * @return
     */
    public HSSFSheet expRqjszrypbqk(HSSFSheet hssfsheet,Object rqjszdj,
            String acceptNo, Long qyid, String username,String flag,HSSFCellStyle cs,String exptype){
    	if("qyxx".equals(exptype)){
    		YwRqQyjyxkFinalRqjszdjb jszdj=(YwRqQyjyxkFinalRqjszdjb) rqjszdj;
    		List<YwRqQyjyxkFinalRqjszrypbqk> entityList =rqjszrypbqkService.getInfosByQyid(jszdj.getId());
            if(entityList == null || entityList.size() <= 0) return null;
            if(entityList!=null)
            {
            try {
                /**
                 * i代表生成的列表行，j表示数据的下标
                 */
                for(int i = 21, j = 0; j< entityList.size(); i++){
                    if(i < 21) continue;
                    
                    YwRqQyjyxkFinalRqjszrypbqk entity = entityList.get(j);
                    if(entity != null){
                        HSSFRow row = hssfsheet.createRow(i);// 创建一个行对象
                        HSSFCell cell0 = row.createCell(1);// 在本行的第一个单元格中写入数据
                        HSSFRichTextString text0 = new HSSFRichTextString(entity.getXm()==null?"":entity.getXm());// 声明文本内容
                        cell0.setCellValue(text0);// 将文本对象加入单元格
                        cell0.setCellStyle(cs);
                        
                        HSSFCell cell1 = row.createCell(2);// 在本行的第一个单元格中写入数据
                        HSSFRichTextString text1 = new HSSFRichTextString(entity.getNl()==null?"":entity.getNl());// 声明文本内容
                        cell1.setCellValue(text1);// 将文本对象加入单元格
                        cell1.setCellStyle(cs);
                        
                        HSSFCell cell2 = row.createCell(3);// 在本行的第一个单元格中写入数据
                        HSSFRichTextString text2 = new HSSFRichTextString(entity.getGw()==null?"":entity.getGw());// 声明文本内容
                        cell2.setCellValue(text2);// 将文本对象加入单元格
                        cell2.setCellStyle(cs);
                        
                        hssfsheet.addMergedRegion(new Region(i,(short)4,i,(short)5)); 
                        HSSFCell cell3 = row.createCell(4);// 在本行的第一个单元格中写入数据
                        HSSFRichTextString text3 = new HSSFRichTextString(entity.getGwjnzsjbh()==null?"":entity.getGwjnzsjbh());// 声明文本内容
                        cell3.setCellValue(text3);// 将文本对象加入单元格
                        cell3.setCellStyle(cs);
                        
                        hssfsheet.addMergedRegion(new Region(i,(short)6,i,(short)7)); 
                        HSSFCell cell4 = row.createCell(6);// 在本行的第一个单元格中写入数据
                        HSSFRichTextString text4 = new HSSFRichTextString(entity.getFzjg()==null?"":entity.getFzjg());// 声明文本内容
                        cell4.setCellValue(text4);// 将文本对象加入单元格
                        cell4.setCellStyle(cs);
                        cell4.setCellStyle(cs);
                    }
                    j++;
                }
            } catch (Exception e){
                e.printStackTrace();
            }
            }
    	}else{
    	    YwRqQyjyxkRqjszdjb jszdj=(YwRqQyjyxkRqjszdjb) rqjszdj;
    		List<YwRqQyjyxkRqjszrypbqk> entityList = this.getListByQyId(jszdj.getId());
            if(entityList == null || entityList.size() <= 0) return null;
            if(entityList!=null)
            {
            try {
                /**
                 * i代表生成的列表行，j表示数据的下标
                 */
                for(int i = 21, j = 0; j< entityList.size(); i++){
                    if(i < 21) continue;
                    
                    YwRqQyjyxkRqjszrypbqk entity = entityList.get(j);
                    if(entity != null){
                        HSSFRow row = hssfsheet.createRow(i);// 创建一个行对象
                        HSSFCell cell0 = row.createCell(1);// 在本行的第一个单元格中写入数据
                        HSSFRichTextString text0 = new HSSFRichTextString(entity.getXm()==null?"":entity.getXm());// 声明文本内容
                        cell0.setCellValue(text0);// 将文本对象加入单元格
                        cell0.setCellStyle(cs);
                        
                        HSSFCell cell1 = row.createCell(2);// 在本行的第一个单元格中写入数据
                        HSSFRichTextString text1 = new HSSFRichTextString(entity.getNl()==null?"":entity.getNl());// 声明文本内容
                        cell1.setCellValue(text1);// 将文本对象加入单元格
                        cell1.setCellStyle(cs);
                        
                        HSSFCell cell2 = row.createCell(3);// 在本行的第一个单元格中写入数据
                        HSSFRichTextString text2 = new HSSFRichTextString(entity.getGw()==null?"":entity.getGw());// 声明文本内容
                        cell2.setCellValue(text2);// 将文本对象加入单元格
                        cell2.setCellStyle(cs);
                        
                        hssfsheet.addMergedRegion(new Region(i,(short)4,i,(short)5)); 
                        HSSFCell cell3 = row.createCell(4);// 在本行的第一个单元格中写入数据
                        HSSFRichTextString text3 = new HSSFRichTextString(entity.getGwjnzsjbh()==null?"":entity.getGwjnzsjbh());// 声明文本内容
                        cell3.setCellValue(text3);// 将文本对象加入单元格
                        cell3.setCellStyle(cs);
                        
                        hssfsheet.addMergedRegion(new Region(i,(short)6,i,(short)7)); 
                        HSSFCell cell4 = row.createCell(6);// 在本行的第一个单元格中写入数据
                        HSSFRichTextString text4 = new HSSFRichTextString(entity.getFzjg()==null?"":entity.getFzjg());// 声明文本内容
                        cell4.setCellValue(text4);// 将文本对象加入单元格
                        cell4.setCellStyle(cs);
                        cell4.setCellStyle(cs);
                        HSSFCell cell5 = row.createCell(5);// 在本行的第一个单元格中写入数据
                        HSSFCell cell7 = row.createCell(7);// 在本行的第一个单元格中写入数据
                        cell5.setCellStyle(cs);
                        cell7.setCellStyle(cs);
                        
                    }
                    j++;
                }
            } catch (Exception e){
                e.printStackTrace();
            }
            }
    	}
        
        return hssfsheet;
    }
    
    private List<YwRqQyjyxkRqjszrypbqk> getListByQyId(Long qyid){
    	  String hql = "from YwRqQyjyxkRqjszrypbqk t where t.gyzdjid='"+qyid+"'";
    	  List<YwRqQyjyxkRqjszrypbqk> list = ywRqQyjyxkRqjszrypbqkDao.find(hql);
          return list;

    }


    
    public Map importFinalQyData(String acceptNo, Long qyid) {
        List<YwRqQyjyxkRqjszrypbqk> list=this.getDatas(acceptNo, qyid);
        for(YwRqQyjyxkRqjszrypbqk entity:list){
            YwRqQyjyxkFinalRqjszrypbqk finalentity = new DozerBeanMapper().map(entity, YwRqQyjyxkFinalRqjszrypbqk.class);
            finalentity.setId(null);
            this.rqjszrypbqkService.save(finalentity);
        }
        return null;
    }
    
    /**
     * 
     * @return
     */
    private List<YwRqQyjyxkRqjszrypbqk> getDatas(String acceptNo, Long qyid){
        String hql = "from YwRqQyjyxkRqjszrypbqk t where t.qyid='" + qyid + "' and t.acceptNo='"+acceptNo+"'";
        return this.ywRqQyjyxkRqjszrypbqkDao.qureyData( acceptNo,  qyid);
    }

    /**
     * 
     */
    public List<YwRqQyjyxkRqjszrypbqk> getDatasByzdid(Long id) {
        String hql = "from YwRqQyjyxkRqjszrypbqk t where t.gyzdjid='" + id + "'";
        return this.ywRqQyjyxkRqjszrypbqkDao.find(hql);
    }

    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkRqjszrypbqkService#findPersonData(java.lang.String, java.lang.String, java.lang.Long)
     */
	public List<Long> findPersonData(String name, String zsbh, Long qyid) {
		 String hql = "select t.qyid from  YwRqQyjyxkRqjszrypbqk t " +
		 		"where t.qyid <>" + qyid + " and xm='"+name+"' and gwjnzsjbh='"+zsbh+"'" ;
		return this.ywRqQyjyxkRqjszrypbqkDao.find(hql);
	}
    
    
    
    
}
